using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Text.RegularExpressions;
using GeneratorLibrary.Client.Base;
using System.Data.Common;

namespace GeneratorLibrary.Client.iSeries {
    public class iSeriesDAC : BaseDAC {

        protected OleDbConnection connection {
            get { return (OleDbConnection)conn; }
        }

        public iSeriesDAC(string strConnection, string schema) : 
            base(new OleDbConnection(strConnection),schema) { 
        }


        private DataTable runSql(string strSql, DbParameter[] parameters) {
            OleDbCommand command = (OleDbCommand)NewCommand(PreExecute(strSql), parameters);
            command.CommandTimeout = 300;
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            DataTable dt = new DataTable();
            try {
                adapter.Fill(dt);
            } catch (Exception ex) {
                throw ex;
            }
            return dt;
        }
        public string PreExecute(string sql){
            Regex r = new Regex("[\\r\\n]+");
            return r.Replace(sql, " ");
        }
        public override void Execute(string sql, DbParameter[] parameters) {
            NewCommand(sql, parameters).ExecuteNonQuery();
        }


        public override DataTable GetColumns(string tableOrViewLike) {
            string strSql = @"SELECT
                t.table_name as TableName, 
                c.column_name as ColumnName, 
                c.data_type AS DataType, 
                c.length, 
                c.Ordinal_Position as ColumnOrder, 
                c.numeric_scale as NumericScale, 
                c.NUMERIC_PRECISION as NumericPrecision, 
                c.CHARACTER_MAXIMUM_LENGTH as CharMaxLength, 
                CASE WHEN c.IS_NULLABLE = 'N' THEN 0 ELSE 1 END as IsNullable,
                CASE WHEN c.IS_IDENTITY = 'YES' or c.IDENTITY_GENERATION = 'ALWAYS' THEN 1 ELSE 0 END as IsIdentity,
                max(case when k.column_name IS NOT NULL then 1 else 0 end) as IsUniqueConstraint,
                max(case when con.constraint_type = 'FOREIGN KEY' AND conCol.column_name IS NOT NULL then 1 else 0 end) 
                    as IsForeignKey,
                max(case when con.constraint_type = 'PRIMARY KEY' AND conCol.column_name IS NOT NULL then 1 else 0 end) 
                    as IsPrimaryKey
            FROM 
                QSYS2.SYSTABLES t
                inner join QSYS2.SYSCOLUMNS c
                    on t.table_name = c.table_name
                    and t.table_schema = c.table_schema
                left join QSYS2.SYSCST con
                    on t.table_name = con.table_name
                    and t.table_schema = con.table_schema
                left join QSYS2.SYSCSTCOL conCol
                    on con.constraint_name = conCol.constraint_name
                    and con.constraint_schema = conCol.constraint_schema
                    and c.column_name = conCol.column_name
                left join QSYS2.SYSINDEXES i
                    on t.table_name = i.table_name
                        and t.table_schema = i.table_schema
                        and i.IS_UNIQUE = 'U' 
                        and i.COLUMN_COUNT=1
                left join QSYS2.SYSKEYS k
                    on i.INDEX_NAME = k.INDEX_NAME 
                        and i.INDEX_SCHEMA = k.INDEX_SCHEMA
                        and c.column_name = k.column_name

            where t.Table_type in ('P','T','V') and t.table_schema = '" + schema + @"' and 
                t.table_name like '" + tableOrViewLike + @"'
            group by t.table_name, c.column_name, c.data_type, c.length, c.Ordinal_Position, c.numeric_scale, c.NUMERIC_PRECISION, c.CHARACTER_MAXIMUM_LENGTH, c.IS_NULLABLE,c.IS_IDENTITY, c.IDENTITY_GENERATION
            order by t.TABLE_NAME,c.Ordinal_Position";

            return runSql(strSql, null);
        }

        public override List<string> GetDatabases() {
            List<string> list = new List<string>();
            OleDbCommand command = new OleDbCommand("select SCHEMA_NAME from qsys2.schemata where SCHEMA_OWNER <> 'QSYS' or SCHEMA_NAME = 'QTEMP' order by SCHEMA_NAME", connection);
            using (IDataReader reader = command.ExecuteReader()) {
                Regex tempTables = new Regex("^T\\d.*$");//SCHEMA_NAME like 'T[0-9]%'
                while (reader.Read()) {
                    string name = reader.GetString(0);
                    if (!tempTables.IsMatch(name))
                        list.Add(name);
                }
            }
            return list;
        }

        public override string GetInserts(string tableLike, bool disableIdentity) {
            StringBuilder sb = new StringBuilder();
            DataTable allTables = runSql("select t.table_name as TableName from QSYS2.SYSTABLES t where t.Table_type in ('P','T') and t.table_schema = '" + schema + @"' and t.table_name like '" + tableLike + "'", null);
            string identityString = "";
            if (!disableIdentity)
                identityString = " and c.IS_IDENTITY = 'NO' ";
            foreach (DataRow tableRow in allTables.Rows) {
                string table = tableRow[0].ToString();
                string columnList = "";
                DataTable columnTable = runSql("SELECT c.column_name as ColumnName, c.data_type AS DataType, case when c.data_type in ('BIGINT','INTEGER','SMALLINT','DECIMAL','NUMERIC','FLOAT') then 0 else 1 end as NeedsQuotes FROM QSYS2.SYSTABLES t inner join QSYS2.SYSCOLUMNS c on t.table_name = c.table_name and t.table_schema = c.table_schema where t.Table_type in ('P','T') and t.table_schema = '" + schema + @"' and table = '" + table + @"' " + identityString + " order by c.Ordinal_Position", null);
                foreach (DataRow row in columnTable.Rows) {
                    columnList += "," + row["ColumnName"];
                }
                columnList = columnList.Remove(0, 1);//remove initial comma

                DataTable dt2 = runSql("select " + columnList + " from " + table, null);

                foreach (DataRow row in dt2.Rows) {
                    sb.Append("insert into " + table);
                    if (disableIdentity)
                        sb.Append(" OVERRIDING USER VALUE ");
                    sb.Append("(" + columnList + ") values (");

                    for (int i = 0; i < columnTable.Rows.Count; i++) {
                        if (i > 0) sb.Append(",");

                        if (row.IsNull(i)) {
                            sb.Append("null");
                        } else if ((int)columnTable.Rows[i]["NeedsQuotes"] == 1) {
                            sb.Append("'");
                            sb.Append(row[i].ToString().Replace("'", "''"));
                            sb.Append("'");
                        } else if ((int)columnTable.Rows[i]["NeedsQuotes"] == -1) {
                            sb.Append(((bool)row[i]) ? 1 : 0);
                        } else {
                            sb.Append(row[i].ToString());
                        }
                    }
                    sb.Append(")" + Environment.NewLine);
                }
            }
            return sb.ToString();
        }

        public override List<string> GetProcs() {
            string strSql = @"SELECT ROUTINE_NAME 
                FROM qsys2.sysprocs 
                where ROUTINE_SCHEMA = '" + schema + @"' 
                order by ROUTINE_NAME";
            return GetList(PreExecute(strSql), null);
        }

        public override List<string> GetTables() {
            string strSql = @"select t.table_name as TableName
                from QSYS2.SYSTABLES t
                where t.table_schema = '" + schema + @"'
                    and t.Table_type in ('P','T')
                order by t.table_name";

            return GetList(PreExecute(strSql), null);
        }

        public override List<string> GetViews() {
            string strSql = @"select t.table_name as TableName
                from QSYS2.SYSTABLES t
                where t.table_schema = '" + schema + @"'
                    and t.Table_type = 'V'
                order by t.table_name";

            return GetList(PreExecute(strSql), null);
        }

        public override List<string> GetProcs(string procLike) {
            string strSql = @"SELECT ROUTINE_NAME 
                FROM qsys2.sysprocs 
                where ROUTINE_SCHEMA = '" + schema + @"' 
                and ROUTINE_NAME like '" + procLike + @"' 
                order by ROUTINE_NAME";
            return GetList(PreExecute(strSql), null);
        }

        protected override DbCommand NewCommand(string strSql, DbParameter[] parameters) {
            OleDbCommand cmd = new OleDbCommand(strSql, connection);
            cmd.CommandTimeout = 300;
            if (parameters != null) {
                foreach (OleDbParameter p in parameters) {
                    cmd.Parameters.Add(p);
                }
            }
            return cmd;
        }
    }
}
